package org.jeecg.modules.message.job;

import lombok.extern.slf4j.Slf4j;
import org.jeecg.modules.demo.pcl.entity.CmsQijuCount;
import org.jeecg.modules.demo.pcl.entity.PclMinmaxMoniter;
import org.jeecg.modules.demo.pcl.mapper.CmsQijuCountMapper;
import org.jeecg.modules.demo.pcl.mapper.PclMinmaxMoniterMapper;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.springframework.beans.factory.annotation.Autowired;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.*;

/**
 * 发送消息任务
 * @author: jeecg-boot
 */

@Slf4j
public class QijuCountJob implements Job {
    @Autowired
    public CmsQijuCountMapper cmsQijuCountMapper;
    @Override
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        // 设置默认时区为本地时区
        TimeZone.setDefault(TimeZone.getTimeZone("Asia/Shanghai")); // 根据实际情况设置时区

        // 注册 JDBC 驱动程序
        try {
            Class.forName("com.ibm.as400.access.AS400JDBCDriver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return;
        }

        // 数据库连接配置
        String url = "jdbc:as400://10.224.80.16;naming=system;libraries=S78AF550;translate binary=true;";
        String user = "Hand_mes";
        String password = "Hand_mes@";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to the DB2 database successfully!");

            // 清空表 PclMinmaxMoniter
//			try (Statement stmt = conn.createStatement()) {
//				stmt.executeUpdate("DELETE FROM pcl_minmax_moniter"); // 清空表
//			}
            cmsQijuCountMapper.deleteAll();

            // 获取结果集
            try (Statement stmt = conn.createStatement()) {
                try (ResultSet rs = stmt.executeQuery(
                        "SELECT " +
                                "TRIM(s.AVPART) AS AVPART, " +
                                "MAX(s2.V6MINQ) AS V6MINQ, " +
                                "MAX(s.AVDES1) AS AVDES1, " +
                                "SUM(s3.BXQTOH) AS BXQTOH_SUM, " +
                                "MAX(s3.BXSTOK) AS BXSTOK, " +
                                "(MAX(s2.V6MINQ) - SUM(s3.BXQTOH)) AS QUESHI, " +
                                "CASE " +
                                "WHEN SUM(s3.BXQTOH) < MAX(s2.V6MINQ) THEN '99' " +
                                "WHEN ABS(MAX(s2.V6MINQ) - SUM(s3.BXQTOH)) <= 10 THEN '98' " + // 改用 ABS()
                                "ELSE '5' END AS COLOR " +
                                "FROM CHCDAT.STKMM s " +
                                "INNER JOIN CHCDAT.STKA s2 ON s2.V6PART = s.AVPART AND s2.V6MINQ != 0 " +
                                "INNER JOIN CHCDAT.STKB s3 ON s3.BXPART = s2.V6PART AND s3.BXSTOK NOT LIKE 'CHCC%' " +
                                "WHERE s.AVMAJG = 'RAK' " +
                                "GROUP BY s.AVPART " +
                                "ORDER BY QUESHI DESC, COLOR"
                )) {
                    List<Record> records = new ArrayList<>();
                    while (rs.next()) {
                        String avpart = rs.getString("AVPART");
                        int v6minq = rs.getInt("V6MINQ");
                        String avdes1 = rs.getString("AVDES1");
                        int bxqtohSum = rs.getInt("BXQTOH_SUM");
                        String bxstok = rs.getString("BXSTOK");
                        int queshi = rs.getInt("QUESHI");
                        String color = rs.getString("COLOR");

                        records.add(new Record(avpart, v6minq, avdes1, bxqtohSum, bxstok, queshi, color));
                    }

                    // 插入数据到新表
                    for (Record record : records) {
                        CmsQijuCount domain = new CmsQijuCount();
                        domain.setAvpart(record.avpart);
                        domain.setAvdes1(record.avdes1);
                        domain.setBxstok(record.bxstok); // 映射 BXSTOK 到 STOCK_TYPE
                        domain.setV6minq(record.v6minq);
                        domain.setBxqtohSum(record.bxqtohSum);
                        domain.setQueshi(record.queshi);
                        domain.setColor(record.color); // 设置颜色
                        cmsQijuCountMapper.insert(domain);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 添加 Record 类来存储查询结果
    private static class Record {
        String avpart;
        int v6minq;
        String avdes1;
        int bxqtohSum;
        String bxstok;
        int queshi;
        String color;

        Record(String avpart, int v6minq, String avdes1, int bxqtohSum, String bxstok, int queshi, String color) {
            this.avpart = avpart;
            this.v6minq = v6minq;
            this.avdes1 = avdes1;
            this.bxqtohSum = bxqtohSum;
            this.bxstok = bxstok;
            this.queshi = queshi;
            this.color = color;
        }
    }

}
